package jsyf.oa.dao.wreport;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import jsyf.oa.entity.wreport.WReportCusVisitEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import jsyf.oa.service.report.reportCommon.ReportCommonMethodsAndConstant;

@Repository
/**
 * @description: 条件查询用户列表
 * @author: @张巨波 @zhangjubo
 * @creat: on 2017/12/18.
 * @since: V1.0.0
 * @see jsyf.oa.web
 */
public class WReportCusVisitDao extends BaseDao {
    private static Integer token = 1;

    @Autowired
    private ReportCommonMethodsAndConstant common;

    public WReportCusVisitDao() {
        super.iBaseEntity = new WReportCusVisitEntity();
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, WReportCusVisitEntity.class);
    }

    public Integer removeByCondition(String condition, String[] params) {
        StringBuffer sql = new StringBuffer();
        sql.append(" DELETE FROM T_WRT_CUSTOMER_VISIT WHERE 1 = 1 \n");
        sql.append(condition);
        return super.delete(sql.toString(), params);
    }


    public JSONObject getEntityByUserId(String userId) {
        StringBuffer sb = new StringBuffer();
        sb.append("select * from t_wrt_customer_visit ");
        sb.append("where CREATE_USER_ID='" + userId + "' ");
        sb.append("AND LEAVE_TIME IS NULL ORDER BY LEAVE_TIME DESC LIMIT 1");
        JSONObject jsonObject = queryForJsonObject(sb.toString());
        return jsonObject == null ? null : jsonObject;
    }

    public JSONObject getByAllCompanyId(String companyId, String dateTime, String yesterday) {
        int len = dateTime.length();
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT\n");
        sb.append(" (SELECT SUM(FACE_TARGET+TEL_TARGET) from t_sys_target\n");
        sb.append(" WHERE COMPANY_ID IN (SELECT ID from t_sys_company WHERE PARENT_ID=" + "'" + companyId + "' \n ");
        sb.append(" AND NAME NOT LIKE '%大客户%')\n");
        if (len == 10) {
            sb.append(" and  TARGET_DATE=" + "'" + dateTime + "'" + " )\n");
        } else {
            sb.append(" and  LEFT(TARGET_DATE,7)=" + "'" + dateTime + "'" + " )\n");
        }
        sb.append(" AS targetDay,\n");
        sb.append(" IFNULL((SELECT SUM(VISIT_MODE=1) FROM t_wrt_customer_visit WHERE \n");
        if (len == 10) {
            sb.append("REPORT_DATE=" + "'" + dateTime + "'\n");
        } else {
            sb.append("LEFT(REPORT_DATE,7)=" + "'" + dateTime + "'\n");
        }
        sb.append(" AND COMPANY_ID IN (SELECT ID from t_sys_company WHERE PARENT_ID=" + "'" + companyId + "'\n");
        sb.append(" AND NAME NOT LIKE '%大客户%')),0)\n");
        sb.append(" +");
        sb.append(" IFNULL((SELECT SUM(VISIT_MODE=0) FROM t_wrt_customer_visit WHERE\n");
        if (len == 10) {
            sb.append("REPORT_DATE=" + "'" + dateTime + "'\n");
        } else {
            sb.append("LEFT(REPORT_DATE,7)=" + "'" + dateTime + "'\n");
        }
        sb.append(" AND COMPANY_ID IN (SELECT ID from t_sys_company WHERE PARENT_ID=" + "'" + companyId + "' \n");
        sb.append(" AND NAME NOT LIKE '%大客户%')),0) AS finishDay \n");
        JSONObject jsonObject = queryForJsonObject(sb.toString());
        return jsonObject == null ? null : jsonObject;
    }

    public JSONObject getByCompanyId(String companyId, String dateTime, String yesterday) {
        int len = dateTime.length();
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT\n");
        sb.append(" (SELECT SUM(FACE_TARGET+TEL_TARGET) from t_sys_target\n");
        sb.append(" WHERE COMPANY_ID IN (SELECT ID from t_sys_company WHERE ID=" + "'" + companyId + "' \n ");
        sb.append(" )\n");
        if (len == 10) {
            sb.append(" and  TARGET_DATE=" + "'" + dateTime + "'" + " )\n");
        } else {
            sb.append(" and  LEFT(TARGET_DATE,7)=" + "'" + dateTime + "'" + " )\n");
        }
        sb.append(" AS targetDay,\n");
        sb.append(" IFNULL((SELECT SUM(VISIT_MODE=1) FROM t_wrt_customer_visit WHERE \n");
        if (len == 10) {
            sb.append("REPORT_DATE=" + "'" + dateTime + "'\n");
        } else {
            sb.append("LEFT(REPORT_DATE,7)=" + "'" + dateTime + "'\n");
        }
        sb.append(" AND COMPANY_ID IN (SELECT ID from t_sys_company WHERE ID=" + "'" + companyId + "'\n");
        sb.append(" )),0)\n");
        sb.append(" +");
        sb.append(" IFNULL((SELECT SUM(VISIT_MODE=0) FROM t_wrt_customer_visit WHERE\n");
        if (len == 10) {
            sb.append("REPORT_DATE=" + "'" + dateTime + "'\n");
        } else {
            sb.append("LEFT(REPORT_DATE,7)=" + "'" + dateTime + "'\n");
        }
        sb.append(" AND COMPANY_ID IN (SELECT ID from t_sys_company WHERE ID=" + "'" + companyId + "' \n");
        sb.append(" )),0) AS finishDay \n");
        JSONObject jsonObject = queryForJsonObject(sb.toString());
        return jsonObject == null ? null : jsonObject;
    }

    public JSONObject getByUserId(String userId, String dateTime, String yesterday) {
        int len = dateTime.length();
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT\n");
        sb.append(" (SELECT SUM(FACE_TARGET+TEL_TARGET) from t_sys_target\n");
        sb.append(" WHERE USER_ID=" + "'" + userId + "' \n ");
        if (len == 10) {
            sb.append(" and  TARGET_DATE=" + "'" + dateTime + "'" + " )\n");
        } else {
            sb.append(" and  LEFT(TARGET_DATE,7)=" + "'" + dateTime + "'" + " )\n");
        }
        sb.append(" AS targetDay,\n");
        sb.append(" IFNULL((SELECT SUM(VISIT_MODE=1) FROM t_wrt_customer_visit WHERE \n");
        if (len == 10) {
            sb.append("REPORT_DATE=" + "'" + dateTime + "'\n");
        } else {
            sb.append("LEFT(REPORT_DATE,7)=" + "'" + dateTime + "'\n");
        }
        sb.append(" AND CREATE_USER_ID=" + "'" + userId + "'\n");
        sb.append(" ),0)+\n");
        sb.append(" IFNULL((SELECT SUM(VISIT_MODE=0) FROM t_wrt_customer_visit WHERE\n");
        if (len == 10) {
            sb.append("REPORT_DATE=" + "'" + dateTime + "'\n");
        } else {
            sb.append("LEFT(REPORT_DATE,7)=" + "'" + dateTime + "'\n");
        }
        sb.append(" AND CREATE_USER_ID=" + "'" + userId + "' \n");
        sb.append(" ),0)AS finishDay\n");
        JSONObject jsonObject = queryForJsonObject(sb.toString());
        return jsonObject == null ? null : jsonObject;
    }

    public JSONObject getByFaceInfo(String dateTime, String companyId) {
        int len = dateTime.length();
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT \n");
        sb.append(" (SELECT SUM(FACE_TARGET) FROM t_sys_target  \n");
        sb.append(" WHERE 1=1 \n");
        if (len == 10) {
            sb.append(" AND TARGET_DATE=" + "'" + dateTime + "'\n");
        } else {
            sb.append(" AND  LEFT(TARGET_DATE,7)= " + "'" + dateTime + "' \n");
        }
        sb.append(" AND t_sys_target.COMPANY_ID =" + "'" + companyId + "' \n");
        sb.append(" ) AS targetDay, ");
        sb.append(" (SELECT count(*) FROM t_wrt_customer_visit \n");
        sb.append(" LEFT JOIN v_sys_user ON t_wrt_customer_visit.CREATE_USER_ID=v_sys_user.id \n");
        sb.append(" WHERE 1=1 ");
        sb.append(" AND t_wrt_customer_visit.VISIT_MODE =1");
        if (len == 10) {
            sb.append(" AND t_wrt_customer_visit.REPORT_DATE=" + "'" + dateTime + "' \n");

        } else {
            sb.append(" AND  LEFT(t_wrt_customer_visit.REPORT_DATE,7)= " + "'" + dateTime + "' \n");
        }
        sb.append(" AND t_wrt_customer_visit.COMPANY_ID =" + "'" + companyId + "' ");
        sb.append(" AND v_sys_user.roleName='区域经理' ");
        sb.append(" ) AS finishDay ");
        JSONObject jsonObject = queryForJsonObject(sb.toString());
        return jsonObject == null ? null : jsonObject;
    }

    public JSONObject getByTelInfo(String dateTime, String yesterday, String companyId) {
        int len = dateTime.length();
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT \n");
        sb.append(" (SELECT SUM(TEL_TARGET) FROM t_sys_target  \n");
        sb.append(" WHERE 1=1 \n");
        if (len == 10) {
            sb.append(" AND TARGET_DATE=" + "'" + dateTime + "'\n");
        } else {
            sb.append(" AND  LEFT(TARGET_DATE,7)= " + "'" + dateTime + "' \n");
        }
        sb.append(" AND t_sys_target.COMPANY_ID =" + "'" + companyId + "' \n");
        sb.append(" ) AS targetDay, ");
        sb.append(" (SELECT count(*) FROM t_wrt_customer_visit \n");
        sb.append(" LEFT JOIN v_sys_user ON t_wrt_customer_visit.CREATE_USER_ID=v_sys_user.id \n");
        sb.append(" WHERE 1=1 ");
        sb.append(" AND t_wrt_customer_visit.VISIT_MODE =0");
        if (len == 10) {
            sb.append(" AND t_wrt_customer_visit.REPORT_DATE=" + "'" + dateTime + "' \n");

        } else {
            sb.append(" AND  LEFT(t_wrt_customer_visit.REPORT_DATE,7)= " + "'" + dateTime + "' \n");
        }
        sb.append(" AND t_wrt_customer_visit.COMPANY_ID =" + "'" + companyId + "' ");
        sb.append(" AND v_sys_user.roleName='区域经理'  ");
        sb.append(" ) AS finishDay ");
        JSONObject jsonObject = queryForJsonObject(sb.toString());
        return jsonObject == null ? null : jsonObject;
    }

    /**
     * 获取某个客户某一天谁在拜访中
     *
     * @return
     */
    public JSONObject getCustomerVistInfo(String customerId, String reportDate) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT a.ID as id,a.CREATE_USER_ID as createUserId,b.`NAME` as salerName, \n");
        sb.append("ARRIVALS_LONGITUDE as arrivalsLongitude,ARRIVALS_LATITUDE as arrivalsLatitude \n");
        sb.append("FROM t_wrt_customer_visit as a  \n");
        sb.append("LEFT JOIN v_sys_user as b on a.CREATE_USER_ID=b.id ");
        sb.append("WHERE REPORT_DATE='" + reportDate + "' AND CUSTOMER_ID='" + customerId + "' \n");
        sb.append("AND LEAVE_TIME IS NULL LIMIT 1 ");
        JSONObject jsonObject = queryForJsonObject(sb.toString());
        return jsonObject == null ? null : jsonObject;
    }


    public JSONObject getLatestCustomerVisitByTel(String userId, String visitMode, String tel) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\t*\n" +
                "FROM\n" +
                "\t`v_wrt_customer_visit`\n" +
                "WHERE\n" +
                "\ttel = '" + tel + "'\n" +
                "AND createUserId = '" + userId + "'\n" +
                "AND visitMode='" + visitMode + "'\n" +
                "ORDER BY\n" +
                "\tarrivalsTime DESC\n" +
                "LIMIT 1 ,\n" +
                " 1;");
        JSONObject jsonObject = queryForJsonObject(sb.toString());
        return jsonObject == null ? null : jsonObject;
    }


    public JSONObject getEntourageDistance(String customerId, String createUserId, String reportDate) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\tbigRegion.CUSTOMER_ID AS customId ,\n" +
                "\tbigRegion.ARRIVALS_TIME AS selfArrTime ,\n" +
                "\tregion.ARRIVALS_TIME AS entourageArrTime ,\n" +
                "\tIFNULL(\n" +
                "\t\tABS(\n" +
                "\t\t\tCEIL(\n" +
                "\t\t\t\tCEILING(\n" +
                "\t\t\t\t\tUNIX_TIMESTAMP(region.ARRIVALS_TIME) - UNIX_TIMESTAMP(bigRegion.ARRIVALS_TIME)\n" +
                "\t\t\t\t) / 60\n" +
                "\t\t\t)\n" +
                "\t\t) ,\n" +
                "\t\t0\n" +
                "\t) AS timeDiff ,\n" +
                "\tbigRegion.ID AS selfVisitId ,\n" +
                "\tbigRegion.ARRIVALS_LATITUDE AS selfArrivalsLatitude ,\n" +
                "\tbigRegion.ARRIVALS_LONGITUDE AS selfArrivalsLongitude ,\n" +
                "\tregion.ID AS entourageVisitId ,\n" +
                "\tregion.ARRIVALS_LATITUDE AS entourageArrivalsLatitude ,\n" +
                "\tregion.ARRIVALS_LONGITUDE AS entourageArrivalsLongitude\n" +
                "FROM\n" +
                "\tt_wrt_customer_visit AS bigRegion\n" +
                "LEFT JOIN t_wrt_customer_visit AS region ON bigRegion.ENTOURAGE_ID = region.CREATE_USER_ID\n" +
                "AND bigRegion.CREATE_USER_ID = region.ENTOURAGE_ID\n" +
                "AND bigRegion.CUSTOMER_ID = region.CUSTOMER_ID\n" +
                "AND bigRegion.REPORT_DATE = region.REPORT_DATE\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND bigRegion.CREATE_USER_ID = '" + createUserId + "'\n" +
                "AND bigRegion.REPORT_DATE = '" + reportDate + "'\n" +
                "AND bigRegion.CUSTOMER_ID = '" + customerId + "'\n" +
                "ORDER BY\n" +
                "\tbigRegion.CREATE_TIME DESC\n" +
                "LIMIT 0 ,\n" +
                " 1");

        JSONObject jsonObject = queryForJsonObject(sb.toString());
        if (jsonObject != null) {
            String selfVisitId = jsonObject.getString("selfVisitId");
            Double selfArrivalsLatitude = jsonObject.getDouble("selfArrivalsLatitude");
            Double selfArrivalsLongitude = jsonObject.getDouble("selfArrivalsLongitude");

            String entourageVisitId = jsonObject.getString("entourageVisitId");
            Double entourageArrivalsLatitude = jsonObject.getDouble("entourageArrivalsLatitude");
            Double entourageArrivalsLongitude = jsonObject.getDouble("entourageArrivalsLongitude");
            if (
                    selfArrivalsLatitude != null &&
                            selfArrivalsLongitude != null &&
                            entourageArrivalsLatitude != null &&
                            entourageArrivalsLongitude != null
            ) {
                //距离差
                String distance = common.getDistanceByLatAndLng(selfArrivalsLatitude, selfArrivalsLongitude, entourageArrivalsLatitude, entourageArrivalsLongitude);
                jsonObject.put("distance", distance);
                return jsonObject;
            } else {
                return null;
            }
        } else {
            return null;
        }
    }
}
